<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    
    <xsl:strip-space elements="text"/>
    
    

    <xsl:template match="Class">
    
PRINT 'Performing work on object [<xsl:value-of select="Name"/>]'
GO

        <xsl:variable name="EMPTY_GUID" select="'00000000-0000-0000-0000-000000000000'"/>
        
  	    <xsl:variable name="Name" select="Name"/>
        <xsl:variable name="SingleCount" select="count(ClassAttribs/ClassAttributes[CType='single'])"/>
        


<xsl:if test="IsNew='true'">

PRINT 'Creating new object <xsl:value-of select="Name"/>'


CREATE TABLE [<xsl:value-of select="Name"/>] (
	[<xsl:value-of select="Name"/>ID] [uniqueidentifier] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Modified] [datetime] NOT NULL,
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
	[<xsl:value-of select="Name"/>] <xsl:value-of select="SqlType"/> NOT NULL,
			</xsl:if>
		</xsl:for-each>
	CONSTRAINT [PK_<xsl:value-of select="Name"/>] PRIMARY KEY CLUSTERED ( [<xsl:value-of select="Name"/>ID] ) ON [PRIMARY]
) ON [PRIMARY]



</xsl:if>


<xsl:for-each select="HeaderInfo/RemovedClasses/Class">

IF object_id ( '<xsl:value-of select="Name"/>' ) IS NOT NULL
BEGIN
	DROP TABLE [<xsl:value-of select="Name"/>]
END

</xsl:for-each>


<xsl:for-each select="RemovedAttributes/ClassAttributes">


PRINT 'Removing field <xsl:value-of select="Name"/> (<xsl:value-of select="Type"/>)'
GO
DECLARE @temp<xsl:value-of select="$Name"/> as varchar(255)
-- Remove Constraint Section
SET @temp<xsl:value-of select="$Name"/> = (select [name] from sysobjects where [type]='D' and [name] like 'DF__<xsl:value-of select="$Name"/>__<xsl:value-of select="Name"/>__%')

IF @temp<xsl:value-of select="$Name"/> IS NOT NULL
BEGIN
	EXEC('alter table [<xsl:value-of select="$Name"/>]  DROP CONSTRAINT ' + @temp<xsl:value-of select="$Name"/>)
END


ALTER TABLE [<xsl:value-of select="$Name"/>] DROP COLUMN [<xsl:value-of select="Name"/>]
GO
	
</xsl:for-each>


<xsl:for-each select="ClassAttribs/ClassAttributes">
		<xsl:if test="IsRemoved='true'">
		
PRINT 'Removing field <xsl:value-of select="Name"/> (<xsl:value-of select="Type"/>) (type based update)'
GO
DECLARE @temp<xsl:value-of select="$Name"/> as varchar(255)
-- Remove Constraint Section
SET @temp<xsl:value-of select="$Name"/> = (select [name] from sysobjects where [type]='D' and [name] like 'DF__<xsl:value-of select="$Name"/>__<xsl:value-of select="Name"/>__%')

IF @temp<xsl:value-of select="$Name"/> IS NOT NULL
BEGIN
	EXEC('alter table [<xsl:value-of select="$Name"/>]  DROP CONSTRAINT ' + @<xsl:value-of select="$Name"/>)
END

ALTER TABLE [<xsl:value-of select="$Name"/>] DROP COLUMN [<xsl:value-of select="Name"/>]
GO
		
		</xsl:if>


		<xsl:if test="IsNew='true'">
		
<xsl:if test="CType!='array'">		
PRINT  'Adding field <xsl:value-of select="Name"/> (<xsl:value-of select="Type"/>)'
GO

ALTER TABLE [<xsl:value-of select="$Name"/>]
ADD [<xsl:value-of select="Name"/>] <xsl:value-of select="SqlSPType"/> NOT NULL
DEFAULT
<xsl:if test="IsEnum='true'">0</xsl:if>
<xsl:if test="Type='int' or Type='double' or Type='bool'">0</xsl:if>
<xsl:if test="(Type='string' or Type='bigstring')">''</xsl:if>
<xsl:if test="Type='DateTime'">getdate()</xsl:if>
<xsl:if test="Type='Guid' or KnownClass='true'">'<xsl:value-of select="$EMPTY_GUID"/>'</xsl:if>
 GO
 
 </xsl:if>
 
 
		</xsl:if>
		
</xsl:for-each>


/*

Sets all NULL fields to a value to not break class

*/


    <xsl:for-each select="ClassAttribs/ClassAttributes">
        <xsl:if test="CType='single'">
            
	<xsl:if test="Type='int' or Type='double'">
UPDATE [<xsl:value-of select="$Name"/>] SET [<xsl:value-of select="Name"/>]=0 WHERE [<xsl:value-of select="Name"/>] IS NULL
GO
            </xsl:if>

            
            <xsl:if test="(Type='string' or Type='bigstring')">
UPDATE [<xsl:value-of select="$Name"/>] SET [<xsl:value-of select="Name"/>]='' WHERE [<xsl:value-of select="Name"/>] IS NULL  
GO
            </xsl:if>
            
            <xsl:if test="Type='DateTime'">
UPDATE [<xsl:value-of select="$Name"/>] SET [<xsl:value-of select="Name"/>]=getdate() WHERE [<xsl:value-of select="Name"/>] IS NULL  
GO
            </xsl:if>
            

            <xsl:if test="Type='Guid' or KnownClass='true'">
UPDATE [<xsl:value-of select="$Name"/>] SET [<xsl:value-of select="Name"/>]='<xsl:value-of select="$EMPTY_GUID"/>' WHERE [<xsl:value-of select="Name"/>] IS NULL                                
GO 
            </xsl:if>
            
            
            
        </xsl:if>
        
        
    </xsl:for-each>




SET QUOTED_IDENTIFIER ON 

SET ANSI_NULLS OFF 




IF object_id('GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>SelectAll') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>SelectAll AS
	    SELECT * FROM [<xsl:value-of select="Name"/>] <xsl:for-each select="ClassAttribs/ClassAttributes[ToString='true']">order by [<xsl:value-of select="Name"/>]</xsl:for-each>
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>SelectAll AS
	    SELECT * FROM [<xsl:value-of select="Name"/>] <xsl:for-each select="ClassAttribs/ClassAttributes[ToString='true']">order by [<xsl:value-of select="Name"/>]</xsl:for-each>
    ')


END

GO



IF object_id('GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>CountAll') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>CountAll AS
	    SELECT COUNT(*) FROM [<xsl:value-of select="Name"/>]
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>CountAll AS
	    SELECT COUNT(*) FROM [<xsl:value-of select="Name"/>]
    ')


END

GO

IF object_id('GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Select') IS NOT NULL
BEGIN

exec('ALTER PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Select (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	SELECT [<xsl:value-of select="Name"/>ID],[created],[modified],
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
				<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]</xsl:if>
				<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
			</xsl:if>
		</xsl:for-each>
	FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
')


END
ELSE
BEGIN

exec('CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Select (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	SELECT [<xsl:value-of select="Name"/>ID],[created],[modified],
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
				<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]</xsl:if>
				<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
			</xsl:if>
		</xsl:for-each>
	FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
	')


END

GO



IF object_id('GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Delete') IS NOT NULL
BEGIN


    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Delete (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	    
	    DELETE FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Delete (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	    DELETE FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
    ')


END

GO



IF object_id('GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Insert') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Insert (@<xsl:value-of select="Name"/>ID uniqueidentifier,
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		    <xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	    </xsl:if>
    </xsl:for-each>
    AS
	    INSERT INTO [<xsl:value-of select="Name"/>]([<xsl:value-of select="Name"/>ID],[created],[modified],
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>])</xsl:if>
		    <xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
	    </xsl:if>
    </xsl:for-each>

	    VALUES(@<xsl:value-of select="Name"/>ID,getDate(),getDate(),
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/>)</xsl:if>
		    <xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/>,</xsl:if>	
	    </xsl:if>
    </xsl:for-each>
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Insert (@<xsl:value-of select="Name"/>ID uniqueidentifier,
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		    <xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	    </xsl:if>
    </xsl:for-each>
    AS
	    INSERT INTO [<xsl:value-of select="Name"/>]([<xsl:value-of select="Name"/>ID],[created],[modified],
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>])</xsl:if>
		    <xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
	    </xsl:if>
    </xsl:for-each>

	    VALUES(@<xsl:value-of select="Name"/>ID,getDate(),getDate(),
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/>)</xsl:if>
		    <xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/>,</xsl:if>	
	    </xsl:if>
    </xsl:for-each>
    ')



END
GO


IF object_id('GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Update')  IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Update (@<xsl:value-of select="Name"/>ID uniqueidentifier,
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		    <xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	    </xsl:if>
    </xsl:for-each>
    AS
	    UPDATE [<xsl:value-of select="Name"/>] SET [modified]=getDate(),
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/></xsl:if>
		    <xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/>,</xsl:if>
	    </xsl:if>
    </xsl:for-each>
    WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Update (@<xsl:value-of select="Name"/>ID uniqueidentifier,
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		    <xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	    </xsl:if>
    </xsl:for-each>
    AS
	    UPDATE [<xsl:value-of select="Name"/>] SET [modified]=getDate(),
    <xsl:for-each select="ClassAttribs/ClassAttributes">
	    <xsl:if test="CType='single'">
		    <xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/></xsl:if>
		    <xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/>,</xsl:if>
	    </xsl:if>
    </xsl:for-each>
    WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
    ')


END


GO


<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="ToString='true'">
        
IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>Search') IS NOT NULL
BEGIN
        
    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>Search(@searchString nvarchar(255))
    AS
	    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like ''%'' + @searchString + ''%''
    ')
		
END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>Search(@searchString nvarchar(255))
    AS
	    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like ''%'' + @searchString + ''%''
    ')
	

END

GO
	</xsl:if>
</xsl:for-each>
        
        
-- DEFINED PROCEDURES FOR CONTENT
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
        <xsl:choose>
            <xsl:when test="Type='bool'">
                
IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>') IS NOT NULL 
BEGIN
                
    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val tinyint)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')

    
END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val tinyint)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')


END

GO
            </xsl:when>
            
            <xsl:when test="Type='DateTime'">
                
IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Between') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Between(@start_ datetime, @end_ datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@start_ and [<xsl:value-of select="Name"/>]&lt;=@end_
    ')
 

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Between(@start_ datetime, @end_ datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@start_ and [<xsl:value-of select="Name"/>]&lt;=@end_
    ')
 

END

GO
                

IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Before') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Before(@val datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
    ')
 

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Before(@val datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
    ')
 

END

GO



IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_After') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_After(@val datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
    ')
 

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_After(@val datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
    ')
 

END

GO

IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
 

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val datetime)
    AS
        SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
 

END

GO

            </xsl:when>
            
            <xsl:when test="Type='int'">
                
IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val int)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
  

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val int)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
  

END

GO

IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val int)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val int)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
    ')


END

GO


IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val int)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val int)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
    ')


END

GO


            </xsl:when>
            
            <xsl:when test="Type='double'">
                
IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>') IS NOT NULL
BEGIN    
    
    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val float)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
  

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val float)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
  

END

GO


IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val float)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val float)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
    ')


END

GO


IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val float)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
    ')


END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val float)
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
    ')


END

GO


            </xsl:when>
            
            
            <xsl:when test="Type='string' and SqlSPType!='ntext'">
                
                
IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>') IS NOT NULL
BEGIN
                
    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val nvarchar(255))
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
   

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val nvarchar(255))
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
    ')
  

END

GO



IF object_id('GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Like') IS NOT NULL
BEGIN

    exec('ALTER PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Like(@val nvarchar(255))
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like ''%'' + @val + ''%''
    ')
   

END
ELSE
BEGIN

    exec('CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Like(@val nvarchar(255))
    AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like ''%'' + @val + ''%''
    ')
   


END

GO
            </xsl:when>
        </xsl:choose>
    </xsl:if>
    
PRINT 'Finished work on Attribute <xsl:value-of select="Name"/>'
</xsl:for-each>



SET QUOTED_IDENTIFIER OFF 

SET ANSI_NULLS ON 

PRINT 'End work on object [<xsl:value-of select="Name"/>]'
GO
























































<![CDATA[


if exists (select * from sysobjects where id = object_id(N'[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Paging_RowCount]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
	SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/	
IF CHARINDEX('DESC',@Sort)>0
	BEGIN
		SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
		SET @operator = '<='
	END
ELSE
	BEGIN
		IF CHARINDEX('ASC', @Sort) = 0
			SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
		SET @operator = '>='
	END


IF CHARINDEX('.', @strSortColumn) > 0
	BEGIN
		SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
		SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
	END
ELSE
	BEGIN
		SET @SortTable = @Tables
		SET @SortName = @strSortColumn
	END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Page Number*/
IF @PageNumber < 1
	SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
	BEGIN
		SET @strFilter = ' WHERE ' + @Filter + ' '
		SET @strSimpleFilter = ' AND ' + @Filter + ' '
	END
ELSE
	BEGIN
		SET @strSimpleFilter = ''
		SET @strFilter = ''
	END
IF @Group IS NOT NULL AND @Group != ''
	SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
	SET @strGroup = ''
	
/*Execute dynamic query*/	
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM [' + @Tables + '] ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM [' + @Tables + '] WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

]]>













































        
    </xsl:template>
    
    
</xsl:stylesheet>